package com.chungbv.dbhelper;

import java.io.IOException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;
import android.widget.Toast;

import com.chungvb.utility.AESEncryptor;
import com.chungvb.utility.Encrypt;
import com.chungvb.utility.SystemData.DATA_TYPE;
import com.karaokevietnam7so.R;
import com.tabactivity.SearchActivity;

public class TestAdapter
{
	protected static final String TAG = "DataAdapter";

	private final Context mContext;
	private SQLiteDatabase mDb;
	private DataBaseHelper mDbHelper;

	public TestAdapter(Context context) {
		this.mContext = context;
		mDbHelper = new DataBaseHelper(mContext);
	}

	public TestAdapter createDatabase() throws SQLException
	{
		try {
			mDbHelper.createDataBase();
		}
		catch (IOException mIOException) {
			Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
			throw new Error("UnableToCreateDatabase");
		}
		return this;
	}

	public TestAdapter open() throws SQLException
	{
		try {
			mDbHelper.openDataBase();
			mDbHelper.close();
			mDb = mDbHelper.getReadableDatabase();
		}
		catch (SQLException mSQLException) {
			Log.e(TAG, "open >>" + mSQLException.toString());
			throw mSQLException;
		}
		return this;
	}

	public void close()
	{
		mDbHelper.close();
	}

	public Cursor getTestData()
	{
		try {
			String sql = "SELECT EmployeeId, Name, Email FROm Employees";

			Cursor mCur = mDb.rawQuery(sql, null);
			if (mCur != null) {
				mCur.moveToNext();
			}
			return mCur;
		}
		catch (SQLException mSQLException) {
			Log.e(TAG, "getTestData >>" + mSQLException.toString());
			throw mSQLException;
		}
	}

	// UPDATE "main"."Songs" SET "KaraokeTypeId" = ?1 WHERE "SongId" = 22950
	// Parameters:
	// param 1 (integer): 3

	public String getMoreCondition(DATA_TYPE data, String text, int type)
	{
		String condition = null;

		switch (data)
		{
			case SEARCH_QUERY_A5:
				condition = " KaraokeTypeId = 1 AND (LanguageId = 1 OR LanguageId = 2)";
				break;

			case SEARCH_QUERY_A6:
				condition = " KaraokeTypeId = 2";
				break;

			case SEARCH_QUERY_A7:
				if (type == 2 || type == 3 || type == 4) {
					return "SELECT * FROM Songs WHERE KaraokeTypeId = 3 AND SongLyricUnsigned GLOB \"*"
							+ text.substring(1) + "*\" ORDER BY SongTitle";
				}
				else {
					condition = " KaraokeTypeId = 3";
					break;
				}

			case SEARCH_JAPANESE:
				condition = " LanguageId = 3";
				break;

			case SEARCH_CHINA:
				condition = " LanguageId = 4";
				break;

			case SEARCH_KOREAN:
				condition = " LanguageId = 5";
				break;

			default:
				/* All data */
				condition = " ";
				break;
		}

		// SongCode
		if (type == 1) {
			String songCode = null;

			try {
				songCode = AESEncryptor.encryptIt(text.substring(1));
			}
			catch (Exception e) {
			}

			return "SELECT * FROM Songs WHERE" + condition + " AND (SongCode = " + text.substring(1)
					+ " OR SongCode = \"" + songCode + "\") ORDER BY SongTitle";
		}

		// SongLyric
		if (type == 2)
			return "SELECT * FROM Songs WHERE" + condition + " AND SongLyricUnsigned GLOB \"*" + text.substring(1)
					+ "*\" ORDER BY SongTitle";

		// SongArtist.
		if (type == 3)
			return "SELECT * FROM Songs,Musicians WHERE Musicians.MusicianDesc LIKE '" + text.substring(1)
					+ "' AND Musicians.MusicianId = Songs.MusicianId AND" + condition + " ORDER BY SongTitle";

		// SongSingers.
		if (type == 4)
			return "SELECT * FROM Songs,Singers WHERE Singers.SingerDesc LIKE \"" + text.substring(1)
					+ "\" AND Singers.SingerId = Songs.SingerId AND" + condition + " ORDER BY SongTitle";

		return null;
	}

	public String getSQLString(DATA_TYPE data, String text)
	{
		String sql = new String();
		String condition = null;

		if (data == DATA_TYPE.SEARCH_QUERY_A5 || data == DATA_TYPE.SEARCH_QUERY_A6 || data == DATA_TYPE.SEARCH_QUERY_A7
				|| data == DATA_TYPE.SEARCH_JAPANESE || data == DATA_TYPE.SEARCH_CHINA
				|| data == DATA_TYPE.SEARCH_KOREAN) {
			if (TextUtils.isEmpty(text)) {
				condition = " ORDER BY SongTitle";
			}
			else {
				// Search by Code number.
				if (text.substring(0, 1).equals("#") && text.length() > 5) {
					return getMoreCondition(data, text, 1);
				}
				// Search by lyric.
				else if (text.substring(0, 1).equals("&") && text.length() > 1) {
					return getMoreCondition(data, text, 2);
				}
				// Search by Artist.
				else if (text.substring(0, 1).equals("@") && text.length() > 1) {
					return getMoreCondition(data, text, 3);
				}
				// Search by Singer.
				else if (text.substring(0, 1).equals("$") && text.length() > 1) {
					return getMoreCondition(data, text, 4);
				}
				// Normal
				else {
					if (SearchActivity.KARA_TYPE_ID == 3 
							|| SearchActivity.KARA_TYPE_ID == 4 
							|| SearchActivity.KARA_TYPE_ID == 5 )
						text = "*" + text;
					
					condition = " AND (SongTitleUnsigned GLOB \"" + text + "*\" OR SongAbbreviation GLOB \"" + text
							+ "*\") ORDER BY SongTitle";
				}
			}
		}

		if (data == DATA_TYPE.PLAYLIST_QUERY && TextUtils.isEmpty(text))
			return null;

		if (data == DATA_TYPE.FAVORITE_QUERY && !TextUtils.isEmpty(text))
			return "SELECT * FROM Songs WHERE isLike = 1 ORDER BY " + text;

		switch (data)
		{
			case SEARCH_QUERY_A5:
				sql = "SELECT * FROM Songs WHERE KaraokeTypeId = 1 AND (LanguageId = 1 OR LanguageId = 2)" + condition;
				break;

			case SEARCH_QUERY_A6:
				sql = "SELECT * FROM Songs WHERE KaraokeTypeId = 2" + condition;
				break;

			case SEARCH_QUERY_A7:
				sql = "SELECT * FROM Songs WHERE KaraokeTypeId = 3" + condition;
				break;

			case FAVORITE_QUERY:
				sql = "SELECT * FROM Songs WHERE isLike = 1 ORDER BY KaraokeTypeId";
				break;

			case PLAYLIST_QUERY:
				sql = text + " ORDER BY SongTitle";
				break;

			case SEARCH_JAPANESE:
				sql = "SELECT * FROM Songs WHERE LanguageId = 3" + condition;

				break;

			case SEARCH_CHINA:
				sql = "SELECT * FROM Songs WHERE LanguageId = 4" + condition;
				break;

			case SEARCH_KOREAN:
				sql = "SELECT * FROM Songs WHERE LanguageId = 5" + condition;
				break;

			default:
				/* All data */
				sql = "SELECT * FROM Songs";
				break;
		}

		return sql;
	}

	public Cursor getData(DATA_TYPE data, String text)
	{
		Log.d("ChungBv", "SQL : " + getSQLString(data, text));
		try {

			if (getSQLString(data, text) == null)
				return null;

			Cursor mCur = mDb.rawQuery(getSQLString(data, text), null);

			if (mCur != null) {
				mCur.moveToNext();
			}

			return mCur;
		}
		catch (SQLException mSQLException) {
			Log.e(TAG, "getTestData >>" + mSQLException.toString());
			throw mSQLException;
		}
	}

	public Cursor getRecortFromID(int type, String data)
	{
		String sql = null;
		switch (type)
		{
			case 0:
				// Artist
				sql = "SELECT MusicianName FROM Musicians WHERE MusicianId = " + data;
				break;
			case 1:
				// Singer
				sql = "SELECT SingerName FROM Singers WHERE SingerId = " + data;
				break;
			case 2:
				// Type
				sql = "SELECT MusicTypeName FROM MusicTypes WHERE MusicTypeId = " + data;
				break;
			case 3:
				// Machine
				sql = "SELECT KaraokeTypename FROM KaraokeTypes WHERE KaraokeTypeId = " + data;
				break;

			default:
				sql = "SELECT * FROM Songs";
				break;
		}

		try {
			// Log.e("ChungBv", "SQL = " + sql);
			Cursor mCur = mDb.rawQuery(sql, null);

			if (mCur != null) {
				mCur.moveToNext();
			}

			return mCur;
		}
		catch (SQLException mSQLException) {
			Log.e(TAG, "getTestData >>" + mSQLException.toString());
			throw mSQLException;
		}
	}

	public Cursor getAllData()
	{
		try {
			String sql = "SELECT * FROM Songs";

			Cursor mCur = mDb.rawQuery(sql, null);
			if (mCur != null) {
				mCur.moveToNext();
			}
			return mCur;
		}
		catch (SQLException mSQLException) {
			Log.e(TAG, "getTestData >>" + mSQLException.toString());
			throw mSQLException;
		}
	}

	public boolean SaveEmployee(String name, String email)
	{
		try {
			ContentValues cv = new ContentValues();
			cv.put("Name", name);
			cv.put("Email", email);

			mDb.insert("Employees", null, cv);

			Log.d("SaveEmployee", "informationsaved");
			return true;

		}
		catch (Exception ex) {
			Log.d("SaveEmployee", ex.toString());
			return false;
		}
	}

	public boolean updateFavorite(String id, String status, String time, boolean notify)
	{
		try {
			ContentValues cv = new ContentValues();
			cv.put("isLike", status);
			cv.put("LastUpdate", time);
			mDb.update("Songs", cv, "SongId = " + id, null);

			if (notify)
				Toast.makeText(
						mContext,
						status.equals("1") ? mContext.getResources().getString(R.string.favorite_addsong) : mContext
								.getResources().getString(R.string.favorite_removesong), Toast.LENGTH_SHORT).show();
			return true;

		}
		catch (Exception ex) {
			Log.d("SaveEmployee", ex.toString());
			return false;
		}

		// return true;
	}

	public void updateSongCode()
	{
		Cursor cur = getConditionUpdate();
		int count = 0;

		do {
//			Log.d("ChungBv", "id=" + cur.getString(0) + " code=" + AESEncryptor.decryptIt(cur.getString(1)));
			Log.d("ChungBv", "id=" + cur.getString(0) + " code=" + getDecrypt(cur.getString(1)) );
			ContentValues cv = new ContentValues();
			cv.put("SongCode", AESEncryptor.encryptIt(getDecrypt(cur.getString(1))));
			mDb.update("Songs", cv, "SongId = " + cur.getString(0), null);
			count++;
		}

		while (cur.moveToNext());

		Log.d("ChungBv", "COUNT = " + count);		
	}

	public String getDecrypt(String en)
	{
		String de = "";
		try {
			de = Encrypt.decrypt("HUE2014", en);
		}
		catch (Exception e) {

		}
		return de;
	}

	public Cursor getConditionUpdate()
	{
		try {
			// OR LanguageId = 3 OR LanguageId = 4 OR LanguageId = 5
			String sql = "SELECT SongId,SongCode FROM Songs WHERE LanguageId = 5";
			Cursor mCur = mDb.rawQuery(sql, null);

			if (mCur != null) {
				mCur.moveToNext();
			}

			return mCur;
		}
		catch (SQLException mSQLException) {
			throw mSQLException;
		}
	}
}
